#!/usr/bin/env python3
# -*- coding: utf-8 -*-

import psycopg2
import requests
import json
import time
import logging
from urllib.parse import unquote

# 配置日志
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(levelname)s - %(message)s',
    handlers=[
        logging.FileHandler('/Volumes/jdssd5/Project/python/pytools/music/music_api_collector.log', encoding='utf-8'),
        logging.StreamHandler()
    ]
)

class MusicAPICollector:
    def __init__(self, db_config):
        self.db_config = db_config
        self.headers = {
            'accept': '*/*',
            'accept-language': 'zh-CN,zh;q=0.9',
            'priority': 'u=1, i',
            'sec-ch-ua': '"Not;A=Brand";v="99", "Google Chrome";v="139", "Chromium";v="139"',
            'sec-ch-ua-mobile': '?0',
            'sec-ch-ua-platform': '"macOS"',
            'sec-fetch-dest': 'empty',
            'sec-fetch-mode': 'cors',
            'sec-fetch-site': 'same-site',
            'User-Agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/139.0.0.0 Safari/537.36'
        }
        
    def connect_db(self):
        """连接数据库"""
        try:
            conn = psycopg2.connect(**self.db_config)
            return conn
        except Exception as e:
            logging.error(f"数据库连接失败: {e}")
            return None
    
    def create_tables_if_not_exists(self):
        """如果表不存在则创建表"""
        conn = self.connect_db()
        if not conn:
            return False
            
        try:
            cursor = conn.cursor()
            
            # 检查表是否存在，如果不存在则创建
            create_music_table = """
            CREATE TABLE IF NOT EXISTS music (
                id BIGSERIAL PRIMARY KEY,
                rid VARCHAR(50) NOT NULL UNIQUE,
                name VARCHAR(255) NOT NULL,
                artist VARCHAR(255) NOT NULL,
                pic VARCHAR(500),
                src VARCHAR(500),
                file_path VARCHAR(500),
                extracted_song_name VARCHAR(255),
                extracted_artist VARCHAR(255),
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            );
            """
            
            create_music_download_url_table = """
            CREATE TABLE IF NOT EXISTS music_download_url (
                id BIGSERIAL PRIMARY KEY,
                music_id BIGINT NOT NULL,
                url_type VARCHAR(50) NOT NULL,
                url_value VARCHAR(500) NOT NULL,
                formatted_url VARCHAR(500) NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (music_id) REFERENCES music(id) ON DELETE CASCADE
            );
            """
            
            create_music_raw_data_table = """
            CREATE TABLE IF NOT EXISTS music_raw_data (
                id BIGSERIAL PRIMARY KEY,
                rid VARCHAR(50) NOT NULL UNIQUE,
                raw_json TEXT NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            );
            """
            
            # 创建索引
            create_indexes = [
                "CREATE UNIQUE INDEX IF NOT EXISTS idx_music_rid ON music(rid);",
                "CREATE UNIQUE INDEX IF NOT EXISTS idx_music_file_path ON music(file_path);",
                "CREATE INDEX IF NOT EXISTS idx_music_name ON music(name);",
                "CREATE INDEX IF NOT EXISTS idx_music_artist ON music(artist);",
                "CREATE INDEX IF NOT EXISTS idx_music_extracted_song_name ON music(extracted_song_name);",
                "CREATE INDEX IF NOT EXISTS idx_music_extracted_artist ON music(extracted_artist);",
                "CREATE INDEX IF NOT EXISTS idx_music_download_url_music_id ON music_download_url(music_id);",
                "CREATE INDEX IF NOT EXISTS idx_music_download_url_url_type ON music_download_url(url_type);",
                "CREATE UNIQUE INDEX IF NOT EXISTS idx_music_raw_data_rid ON music_raw_data(rid);"
            ]
            
            # 执行创建表的SQL
            cursor.execute(create_music_table)
            cursor.execute(create_music_download_url_table)
            cursor.execute(create_music_raw_data_table)
            
            # 创建索引
            for index_sql in create_indexes:
                cursor.execute(index_sql)
            
            conn.commit()
            cursor.close()
            conn.close()
            logging.info("数据库表检查/创建完成")
            return True
        except Exception as e:
            logging.error(f"创建数据库表失败: {e}")
            if conn:
                conn.rollback()
                conn.close()
            return False
    
    def fetch_hot_songs(self):
        """获取热门歌曲列表"""
        try:
            url = "https://api.qqmp3.vip/api/songs.php"
            response = requests.get(url, headers=self.headers, timeout=10)
            if response.status_code == 200:
                data = response.json()
                if data.get('code') == 200:
                    logging.info(f"成功获取热门歌曲列表，共 {len(data.get('data', []))} 首歌曲")
                    return data.get('data', [])
                else:
                    logging.error(f"获取热门歌曲列表失败: {data.get('message', '未知错误')}")
            else:
                logging.error(f"HTTP请求失败，状态码: {response.status_code}")
        except Exception as e:
            logging.error(f"获取热门歌曲列表时出错: {e}")
        return []
    
    def fetch_random_songs(self):
        """获取随机推荐歌曲"""
        try:
            url = "https://api.qqmp3.vip/api/songs.php?type=rand"
            response = requests.get(url, headers=self.headers, timeout=10)
            if response.status_code == 200:
                data = response.json()
                if data.get('code') == 200:
                    logging.info(f"成功获取随机推荐歌曲，共 {len(data.get('data', []))} 首歌曲")
                    return data.get('data', [])
                else:
                    logging.error(f"获取随机推荐歌曲失败: {data.get('message', '未知错误')}")
            else:
                logging.error(f"HTTP请求失败，状态码: {response.status_code}")
        except Exception as e:
            logging.error(f"获取随机推荐歌曲时出错: {e}")
        return []
    
    def save_music_to_db(self, song_data):
        """将音乐数据保存到数据库"""
        conn = self.connect_db()
        if not conn:
            return False
            
        try:
            cursor = conn.cursor()
            
            # 插入音乐数据
            insert_music_sql = """
            INSERT INTO music (rid, name, artist, pic, src, file_path, extracted_song_name, extracted_artist)
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
            ON CONFLICT (rid) DO UPDATE SET
                name = EXCLUDED.name,
                artist = EXCLUDED.artist,
                pic = EXCLUDED.pic,
                src = EXCLUDED.src,
                updated_at = CURRENT_TIMESTAMP
            RETURNING id;
            """
            
            cursor.execute(insert_music_sql, (
                song_data.get('rid'),
                song_data.get('name'),
                song_data.get('artist'),
                song_data.get('pic').replace('\\', '') if song_data.get('pic') else None,
                song_data.get('src').replace('\\', '') if song_data.get('src') else None,
                None,  # file_path
                song_data.get('name'),  # extracted_song_name
                song_data.get('artist')  # extracted_artist
            ))
            
            music_id = cursor.fetchone()[0]
            conn.commit()
            
            # 插入下载链接数据
            if 'downurl' in song_data and song_data['downurl']:
                for downurl_item in song_data['downurl']:
                    if '$$' in downurl_item:
                        url_type, url_value = downurl_item.split('$$', 1)
                        formatted_url = f"{url_type}: {url_value}"
                        
                        insert_download_url_sql = """
                        INSERT INTO music_download_url (music_id, url_type, url_value, formatted_url)
                        VALUES (%s, %s, %s, %s)
                        ON CONFLICT DO NOTHING;
                        """
                        
                        cursor.execute(insert_download_url_sql, (
                            music_id,
                            url_type,
                            url_value,
                            formatted_url
                        ))
            
            # 保存原始JSON数据
            insert_raw_data_sql = """
            INSERT INTO music_raw_data (rid, raw_json)
            VALUES (%s, %s)
            ON CONFLICT (rid) DO UPDATE SET
                raw_json = EXCLUDED.raw_json;
            """
            
            cursor.execute(insert_raw_data_sql, (
                song_data.get('rid'),
                json.dumps(song_data, ensure_ascii=False)
            ))
            
            conn.commit()
            cursor.close()
            conn.close()
            
            logging.info(f"成功保存歌曲: {song_data.get('name')} - {song_data.get('artist')}")
            return True
        except Exception as e:
            logging.error(f"保存歌曲数据到数据库时出错: {e}")
            if conn:
                conn.rollback()
                conn.close()
            return False
    
    def collect_music_data(self, max_songs=100):
        """收集音乐数据"""
        # 首先确保数据库表存在
        if not self.create_tables_if_not_exists():
            logging.error("无法创建数据库表")
            return
            
        # 获取热门歌曲
        hot_songs = self.fetch_hot_songs()
        logging.info(f"获取到 {len(hot_songs)} 首热门歌曲")
        
        # 获取随机推荐歌曲
        random_songs = self.fetch_random_songs()
        logging.info(f"获取到 {len(random_songs)} 首随机推荐歌曲")
        
        # 合并歌曲列表并去重
        all_songs = {}
        for song in hot_songs + random_songs:
            if 'rid' in song:
                all_songs[song['rid']] = song
        
        logging.info(f"合并后共有 {len(all_songs)} 首不重复歌曲")
        
        # 保存歌曲数据到数据库
        saved_count = 0
        for i, song in enumerate(list(all_songs.values())[:max_songs]):
            try:
                logging.info(f"处理进度: {i+1}/{min(len(all_songs), max_songs)} - {song.get('name')} - {song.get('artist')}")
                
                if self.save_music_to_db(song):
                    saved_count += 1
                    
                # 添加延迟避免请求过于频繁
                time.sleep(0.5)
                
            except Exception as e:
                logging.error(f"处理歌曲时出错: {e}")
        
        logging.info(f"数据收集完成，成功保存 {saved_count} 首歌曲")

def main():
    # 数据库配置
    db_config = {
        'host': 'localhost',
        'database': 'postgres',
        'user': 'lijun',
        'password': 'kuaile9hao',
        'port': 5432
    }
    
    # 创建收集器实例
    collector = MusicAPICollector(db_config)
    
    # 收集音乐数据
    collector.collect_music_data(max_songs=100)

if __name__ == "__main__":
    main()